package com.sp.scmdb.serviceImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.Lists;
import com.sp.scmdb.bean.OriginalBean;
import com.sp.scmdb.common.CommonParamter;
import com.sp.scmdb.dao.AlarmDao;
import com.sp.scmdb.dao.AlarmTypeDao;
import com.sp.scmdb.dao.AreaDao;
import com.sp.scmdb.dao.AreaDataDao;
import com.sp.scmdb.dao.CallAlarmDao;
import com.sp.scmdb.dao.EmployeeDao;
import com.sp.scmdb.dao.EntryRecordDao;
import com.sp.scmdb.dao.His_OriginalDao;
import com.sp.scmdb.dao.LabelIODao;
import com.sp.scmdb.dao.LabelReadingMachineDao;
import com.sp.scmdb.dao.OriginalDao;
import com.sp.scmdb.dao.ParameterDao;
import com.sp.scmdb.dao.ReaderAlarmDao;
import com.sp.scmdb.dao.RelEmpAreaDao;
import com.sp.scmdb.entity.Alarm;
import com.sp.scmdb.entity.AlarmType;
import com.sp.scmdb.entity.Area;
import com.sp.scmdb.entity.AreaData;
import com.sp.scmdb.entity.CallAlarm;
import com.sp.scmdb.entity.Employee;
import com.sp.scmdb.entity.EntryRecord;
import com.sp.scmdb.entity.His_Original;
import com.sp.scmdb.entity.LabelIO;
import com.sp.scmdb.entity.LabelReadingMachine;
import com.sp.scmdb.entity.Original;
import com.sp.scmdb.entity.Parameter;
import com.sp.scmdb.entity.Reader_Alarm;
import com.sp.scmdb.entity.RelEmpArea;
import com.sp.scmdb.service.ParseDataService;
import com.sp.scmdb.utils.DBConnection;

@Service("parseDataService")
public class ParseDataServiceImpl implements ParseDataService {

	private static Log log = LogFactory.getLog(ParseDataServiceImpl.class);
	private OriginalDao originalDao;
	private LabelIODao labelDao;
	private His_OriginalDao his_originalDao;
	private AlarmDao alarmDao;
	private LabelReadingMachineDao readerDao;
	private AreaDao areaDao;
	private ParameterDao parameterDao;
	private EmployeeDao empDao;
	private AreaDataDao adDao;
	private AlarmTypeDao aTDao;
	private RelEmpAreaDao empAreaDao;
	private ReaderAlarmDao raDao;
	private CallAlarmDao callAlarmDao;
	private EntryRecordDao entryDao;

	@Required
	public void setEntryDao(EntryRecordDao entryDao) {
		this.entryDao = entryDao;
	}

	@Required
	public void setCallAlarmDao(CallAlarmDao callAlarmDao) {
		this.callAlarmDao = callAlarmDao;
	}

	@Required
	public void setRaDao(ReaderAlarmDao raDao) {
		this.raDao = raDao;
	}

	@Required
	public void setEmpAreaDao(RelEmpAreaDao empAreaDao) {
		this.empAreaDao = empAreaDao;
	}

	@Required
	public void setATDao(AlarmTypeDao dao) {
		aTDao = dao;
	}

	@Required
	public void setAdDao(AreaDataDao adDao) {
		this.adDao = adDao;
	}

	@Required
	public void setEmpDao(EmployeeDao empDao) {
		this.empDao = empDao;
	}

	@Required
	public void setParameterDao(ParameterDao parameterDao) {
		this.parameterDao = parameterDao;
	}

	@Required
	public void setRegionalDao(AreaDao areaDao) {
		this.areaDao = areaDao;
	}

	@Required
	public void setHis_originalDao(His_OriginalDao his_originalDao) {
		this.his_originalDao = his_originalDao;
	}

	@Required
	public void setReaderDao(LabelReadingMachineDao readerDao) {
		this.readerDao = readerDao;
	}

	@Required
	public void setLabelDao(LabelIODao labelDao) {
		this.labelDao = labelDao;
	}

	@Required
	public void setAlarmDao(AlarmDao alarmDao) {
		this.alarmDao = alarmDao;
	}

	@Required
	public void setOriginalDao(OriginalDao originalDao) {
		this.originalDao = originalDao;
	}

	@Transactional(readOnly = true)
	public List<Reader_Alarm> readerAlarm(String Hql) {
		return raDao.queryHql(Hql);
	}

	@Transactional(readOnly = true)
	public List<LabelIO> queryLabel(String Hql) {
		return labelDao.queryHql(Hql);
	}
	
	@Transactional(readOnly = true)
	public List<His_Original> findHisOriginal(String hql) {
		return his_originalDao.queryHql(hql);
	}
	
	@Transactional(readOnly = true)
	public List<EntryRecord> findEntryRecord(String hql) {
		return entryDao.queryHql(hql);
	}
	@Transactional
	public void updateEntryRecord(EntryRecord entryRecord) {
		entryDao.update(entryRecord);
	}

	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<Area> queryRegional() {
		return areaDao.queryAll();
	}

	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<Parameter> queryParameter(String Hql) {
		return parameterDao.queryHql(Hql);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List findSql(String Sql) {
		return labelDao.findLabel(Sql);
	}

	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<Employee> findEmp(String Sql) {
		return empDao.queryHql(Sql);
	}

	@Transactional(readOnly = true)
	public List<AreaData> findData(String Sql) {
		return adDao.queryHql(Sql);
	}

	@Transactional(readOnly = true)
	public List<LabelReadingMachine> findReader() {
		return readerDao.queryAll();
	}
	
	@Transactional(readOnly = true)
	public List<LabelReadingMachine> findReader(String hql) {
		return readerDao.queryHql(hql);
	}
	
	@Transactional(readOnly = true)
	public void findReaderFault(LabelReadingMachine reader) {
		String strNow = new DateTime().plusMinutes(5).toString();
		String hql = "from His_Original where lrmID = " + reader.getLrmID() + " and dataTime >= '" + strNow + "'";
		hql += " and dataInfo like '%%' ";
		
		List<His_Original> hisOriginalList = findHisOriginal(hql);
		
	}
	
	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<AlarmType> alarmType() {
		return aTDao.queryAll();
	}

	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<RelEmpArea> empAreaList(String Hql) {
		return empAreaDao.queryHql(Hql);
	}

	@Transactional
	public void saveAlarm(Alarm alarm) {
		alarmDao.save(alarm);
	}

	@Transactional
	public void updateAlarm(Alarm alarm) {
		alarmDao.update(alarm);
	}

	@Transactional
	public void saveReaderAlarm(Reader_Alarm ra) {
		raDao.save(ra);
	}

	@Transactional
	public void updateReaderAlarm(Reader_Alarm ra) {
		raDao.update(ra);
	}

	public boolean SingleTransaction(OriginalBean or, Map<String, LabelReadingMachine> mapReader,
			Map<Integer, AlarmType> mapAlarmType, List<Area> areaList) {
		try {
			LabelReadingMachine lrmBean = mapReader.get(String.valueOf(or.getLrmID()));
			// 没有读卡器
			if (lrmBean == null) {
				return true;
			}
			// 卡号没有人员关联
			String sql = "from Employee where empLableID='" + or.getLabelID() + "'";
			List<Employee> listEmp = findEmp(sql);
			if (CollectionUtils.isNotEmpty(listEmp)) {
				Employee emp = new Employee();
				emp = listEmp.get(0);
				// 卡片报警 （待定）
				if (or.getCallAlarm() == CommonParamter.ParseData.powerAlarm) {
					InsertCallAlarm(or, emp, lrmBean);
				}
				// 写入低电量报警
				if (or.getPowerAlarm() == CommonParamter.ParseData.powerAlarm) {
					InsertPowerAlarm(or, emp, 1, lrmBean, mapAlarmType);
				}
				// 园内读卡器
				coalMineDown(or, emp, areaList);
			}
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
		return true;
	}

	// 操作紧急报警
	@Transactional
	public void InsertCallAlarm(OriginalBean or, Employee emp, LabelReadingMachine lrmBean) {
		try {
			CallAlarm calAlarm = new CallAlarm();
			List<CallAlarm> callAlarmList = callAlarmDao
					.queryHql("from CallAlarm where empID=" + emp.getId());
			if (CollectionUtils.isNotEmpty(callAlarmList)) {
				calAlarm = callAlarmList.get(0);
				calAlarm.setLrmID(or.getLrmID());
				calAlarm.setLrmName(lrmBean.getLrmName());
				calAlarm.setAlarmDate(new DateTime());
				calAlarm.setConfirmDate(null);
			} else {
				calAlarm.setEmpID(emp.getId());
				calAlarm.setLabelID(String.valueOf(or.getLabelID()));
				calAlarm.setLrmID(or.getLrmID());
				calAlarm.setLrmName(lrmBean.getLrmName());
				calAlarm.setAlarmDate(new DateTime());
				if(emp.getWorkType().getId()==31){
					calAlarm.setRmk("2");
				}else{
					calAlarm.setRmk("9");
				}
			}
			callAlarmDao.saveOrUpdate(calAlarm);
		} catch (Exception e) {
			log.error("InsertCallAlarm error:" + e.getMessage());
		}
	}

	@Transactional
	public void coalMineDown(OriginalBean or, Employee emp, List<Area> areaList) {
		try {
			List<LabelIO> labelList = Lists.newArrayList();
			String sql = "from LabelIO where empID=" + emp.getId() + "";
			labelList = labelList(sql);
			insertLabelIn(or, emp, areaList, labelList);

		} catch (Exception e) {
			throw new IllegalStateException("operator coalMineDown error:" + e.getMessage());
		}

	}

	public boolean alarmBean(OriginalBean or, Employee emp, int state, LabelReadingMachine lrmBean) {
		Alarm alarm = new Alarm();
		alarm.setAlarmType(state);
		alarm.setEmpID(emp.getId());
		alarm.setInTime(or.getData_Time());
		alarm.setRmk("游客 " + emp.getEmpName() + " 在 " + lrmBean.getLrmName() + " 报警");
		alarmDao.save(alarm);
		return true;
	}

	// 操作报警
	@Transactional
	public void InsertPowerAlarm(OriginalBean or, Employee emp, int state, LabelReadingMachine lrmBean,
			Map<Integer, AlarmType> mapAlarmType) {
		try {
			if (mapAlarmType.get(state) != null && mapAlarmType.get(state).getIsUse().equals("1")) {
				String Parameter = "and empID = " + emp.getId() + " and alarmType=1";
				List<Alarm> listAlarm = alarmList(Parameter);
				if (CollectionUtils.isEmpty(listAlarm)) {
					alarmBean(or, emp, state, lrmBean);
				}
			}
			// 如果不是低电量报警在下面做其他操作
		} catch (Exception e) {
			log.error("InsertPowerAlarm error:" + e.getMessage());
		}
	}

	public void optionArea(OriginalBean or, Employee emp, List<Area> areaList) {
		int reID = 0;
		List<AreaData> areaData = adDao.queryHql("from AreaData where empID=" + emp.getId());
		if (CollectionUtils.isNotEmpty(areaList)) {
			for (int ar = 0; ar < areaList.size(); ar++) {
				Area area = areaList.get(ar);
				if (("," + area.getReaders() + ",").indexOf("," + or.getLrmID() + ",") >= 0) {
					reID = area.getId();
					break;
				}
			}
		}
		if (CollectionUtils.isEmpty(areaData)) {
			AreaData ad = new AreaData();
			ad.setReID(reID);
			ad.setEmpID(emp.getId());
			ad.setInTime(or.getData_Time());
			adDao.save(ad);
		} else {
			AreaData data = new AreaData();
			data = areaData.get(0);
			if (data.getReID() != reID) {
				data.setReID(reID);
				data.setInTime(or.getData_Time());
				adDao.update(data);
			}
		}
	}

	// 获取报警信息
	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<LabelIO> labelList(String Parameter) {
		List<LabelIO> labelList = Lists.newArrayList();
		labelList = labelDao.queryHql(Parameter);
		return labelList;
	}

	// 获取轨迹信息
	@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
	public List<Alarm> alarmList(String Parameter) {
		List<Alarm> listAlarm = alarmDao.queryHql("from Alarm where 1=1 " + Parameter);
		return listAlarm;
	}

	// 获取历史数据
	public void removeLabelList() {
		DateTime dt = new DateTime();
		String sql = "from LabelIO where inTime<'"
				+ dt.toString("yyyy-MM-dd") + "' and outTime is null";
		List<LabelIO> newList = labelList(sql);
		if (CollectionUtils.isNotEmpty(newList)) {
			for (LabelIO labelIO : newList) {
				hisLabelIOBean(labelIO);
			}
		}
		sql = "insert into OBJ_HIS_LABELIO SELECT TOP 1000 empID, labelID, lrmID, inTime, outTime, rmk from OBJ_LABELIO where inTime<'" + dt.toString("yyyy-MM-dd") + "'";
		labelDao.excute(sql);
		sql = "delete top (1000) from OBJ_LABELIO where inTime<'" + dt.toString("yyyy-MM-dd") + "'";
		labelDao.excute(sql);
		sql = "delete top (1000) from OBJ_ENTRYRECORD where inTime<'" + dt.toString("yyyy-MM-dd") + "'";
		labelDao.excute(sql);
		delAreaData();
	}

	public void entryBean(OriginalBean or, Employee emp) {
		EntryRecord entry = new EntryRecord();
		entry.setEmpID(emp.getId());
		entry.setLabelID(or.getLabelID());
		entry.setLrmID(or.getLrmID());
		entry.setInTime(or.getData_Time());
		entry.setRmk(or.getLabelType());
		entryDao.save(entry);
	}

	public void LabelIOBean(OriginalBean or, Employee emp) {
		LabelIO label = new LabelIO();
		label.setEmpID(emp.getId());
		label.setLabelID(or.getLabelID());
		label.setLrmID(or.getLrmID());
		label.setInTime(or.getData_Time());
		label.setRmk(or.getLabelType());
		labelDao.save(label);
	}

	public void hisLabelIOBean(LabelIO labelIO) {
		labelIO.setOutTime(labelIO.getInTime().plusMinutes(1));
		labelDao.update(labelIO);
	}

	// 操作轨迹
	public void insertLabelIn(OriginalBean or, Employee emp, List<Area> areaList, List<LabelIO> labelList) {
		if (CollectionUtils.isNotEmpty(labelList)) {
			LabelIO labelio = labelList.get(labelList.size() - 1);
			if (labelio.getOutTime() == null 
					&& or.getData_Time().isAfter(labelio.getInTime().plusSeconds(10))) {
				if(labelio.getLrmID() != or.getLrmID()){
					labelio.setOutTime(or.getData_Time());
					labelDao.update(labelio);
					// 添加轨迹
					LabelIOBean(or, emp);
				}
				List<EntryRecord> listEntry = entryDao.queryHql(" from EntryRecord where empID="
						+ emp.getId() + "");
				if (CollectionUtils.isNotEmpty(listEntry)) {
					EntryRecord er = listEntry.get(0);
					er.setInTime(or.getData_Time());
					er.setLrmID(or.getLrmID());
					entryDao.update(er);
				}
			}
		} else {
			// 添加轨迹
			LabelIOBean(or, emp);
			// 添加实时数据
			entryBean(or, emp);
		}
		// 区域操作
		optionArea(or, emp, areaList);
	}

	public void delAreaData() {
		try {
			DateTime dt = new DateTime();
			String sql = "delete top (1000) from OBJ_AREA_DATA where inTime<'"+dt.toString("yyyy-MM-dd")+"'";
			labelDao.excute(sql);
			sql = "delete from OBJ_ALARM where rmk like '%游客%'";
			labelDao.excute(sql);
			sql = "delete from OBJ_CALLALARM where rmk='9' and alarmDate<'"+dt.toString("yyyy-MM-dd")+"'";
			labelDao.excute(sql);
			sql = "update OBJ_CALLALARM set confirmDate = '"+dt.toString("yyyy-MM-dd HH:mm:ss")+"' where alarmDate<'"+dt.toString("yyyy-MM-dd")+"'";
			labelDao.excute(sql);
			sql = "update OBJ_ALARM set state=1, outTime = '"+dt.toString("yyyy-MM-dd HH:mm:ss")+"' where inTime<'"+dt.toString("yyyy-MM-dd")+"'";
			labelDao.excute(sql);
		} catch (Exception e) {
			throw new IllegalStateException(e.getMessage());
		}
	}

	@Transactional
	public void delOriginalBean(OriginalBean or) {
		try {
			His_Original his_original = new His_Original();
			his_original.setDataInfo(or.getData_Info());
			his_original.setDataTime(or.getData_Time());
			his_original.setCallAlarm(or.getCallAlarm());
			his_original.setLabelType(or.getLabelType());
			his_original.setLrmID(or.getLrmID());
			his_original.setLabelID(or.getLabelID());
			his_original.setPowerAlarm(or.getPowerAlarm());
			his_original.setPowerFlag(or.getPowerFlag());
			his_originalDao.save(his_original);
			Original original = new Original();
			original.setId(or.getId());
			originalDao.delete(original);
		} catch (Exception e) {
			throw new IllegalStateException("delOriginalBean error:" + e.getMessage());
		}
	}
	
	
	//判断是否已离开门禁系统
	public boolean isOutCardRecord(int empID) {
		Connection con = DBConnection.getConnection();
		try {
			int h = 12;
			if(con == null) return true;
			String sql = "select * from";
			sql += " (";
			sql += " select CONVERT(varchar(25), MAX(ioDate), 20) sdate, 1 sort from card_originalrecord where empID = ? and DoorIndex = '1'";
			sql += " union all";
			sql += " select CONVERT(varchar(25), MAX(ioDate), 20) sdate, 2 sort from card_originalrecord where empID = ? and DoorIndex <> '1'";
			sql += " )tab order by tab.sort asc";
			PreparedStatement pre = con.prepareStatement(sql);
			pre.setInt(1, empID);
			pre.setInt(2, empID);
			ResultSet res = pre.executeQuery();
			String[] str = new String[2];
			int i = 0;
			while(res.next()) {
				if(i == 0) str[0] = res.getString("sdate"); //进入时间
				if(i == 1) str[1] = res.getString("sdate"); //离开时间
				i++;
			}
			DateTimeFormatter formatter =DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
			DateTime now = formatter.parseDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
			if(str[0] != null && str[1] != null) {
				if(formatter.parseDateTime(str[1]).isAfter(formatter.parseDateTime(str[0]))) { //离开时间在进入时间之后则算离开
					return true; 
				}  else if(formatter.parseDateTime(str[1]).isBefore(formatter.parseDateTime(str[0]))) { //离开时间在进入时间之前, 说明本次进入还未离开
					DateTime inTime = formatter.parseDateTime(str[0]).plusHours(h);
					if(inTime.isAfter(now)) { //加了12小时大于当前时间则算还未离开
						return false;
					}
				} else {
					return true;
				}
			}
	
			if(str[0] != null && str[1] == null) { //说明第一次刷卡进入
				//未离开门禁刷卡, 如果进入时间与系统当前时间相差大于等于12小时则算离开
				DateTime inTime = formatter.parseDateTime(str[0]).plusHours(h);
				if(inTime.isAfter(now)) {
					return false;
				}
			}
		} catch (Exception e) {
			log.error("判断卡号是否已离开门禁系统出错:" + e.getMessage());
			return true;
		} finally {
			try {
				if(!con.isClosed()) {
					con.close();
				}
			} catch (Exception e2) {
				log.error("==" + e2.getMessage());
				return true;
			}
		}
		return true;
	}
	
	public void execute(String sql) {
		his_originalDao.excute(sql);
	}
}
